<?php
namespace Kuoqi\Api;

use Kuoqi\Exception;
use Kuoqi\Route;
/**
 * 这里是phpexcel的基础api的相关控制
 * Class PhpExcel
 * @package Kuoqi\Api
 * User kuoqi
 * Datetime 2022/3/30 10:09
 * Remark
 */
class PhpExcel extends \Kuoqi\Api{

    /**
     * 入口文件路径
     * @var string
     * User kuoqi
     * Datetime 2022/3/30 11:24
     * Remark
     */
    public $index_root = '{@ROOT}/library/api/php-excel/PHPExcel.php';

    /**
     * 输出转存数据
     * @var array
     * User kuoqi
     * Datetime 2022/3/30 11:26
     * Remark
     */
    public $output_data = array();

    /**
     * 常规格式输出
     * @var string
     * User kuoqi
     * Datetime 2022/3/30 11:26
     * Remark
     */
    public $format = '';

    /**
     * 不同格式【format】配合的参数信息
     * @var array
     * User kuoqi
     * Datetime 2022/3/30 11:28
     * Remark
     */
    public $opts = array();

    /**
     * 设置一个文件名称
     * @var string
     * User kuoqi
     * Datetime 2022/4/2 16:18
     * Remark
     */
    public $filename = '';

    //初始化信息
    public function __construct()
    {
        $this->init();
        $this->filename = date('YmdHis');
        if(!$this->isValied()){
            throw new \Kuoqi\Error($this->getError());
        }

        //加载成功之后进行什么操作呢
        //todo--执行什么操作呢，

    }

    /**
     * 尝试格式化为列表
     * @param array $opts
     * User kuoqi
     * Datetime 2022/3/30 11:30
     * Remark
     */
    public function formatList($opts = array()){
        //格式化为数组信息
        $this->format = 'data_list';//列表数据模式
        $this->optsFormatList($opts);
    }

    /**
     * 减压formatList的数据基础整合
     * @param $opts
     * User kuoqi
     * Datetime 2022/4/2 15:11
     * Remark
     */
    private function optsFormatList($opts){
        $this->opts = array(
            'is_row'=>0,//是否提供标题，如果提供标题则使用具体的数字代表第几个

            //具体的标题定义字段
            'cols'=>array(
                //'label'=>'code'，输入的字段对应输出的字段名
            ),

            'col_require'=>'',//单行所必须的字段，如果该字段为空，则废弃本行输出

            'sheet_name'=>'',//获取单sheet的名称，没有的话，自动获取当前活动的一个sheet
        );

        if(isset($opts['is_row'])){
            $this->opts['is_row'] = $opts['is_row'];
        }

        if(isset($opts['cols'])){
            $this->opts['cols'] = $opts['cols'];
        }

        if(isset($opts['col_require'])){
            $this->opts['col_require'] = $opts['col_require'];
        }

        if(isset($opts['sheet_name'])){
            $this->opts['sheet_name'] = $opts['sheet_name'];
        }

    }

    /**
     * 初始化一个数据信息 返回当前活跃的sheet对象信息
     * createby kuoqi at 2022/9/20 13:47
     * @param string $sheetname
     * @return \PHPExcel_Worksheet
     * @throws \PHPExcel_Exception
     */
    public function create($sheetname = ''){
        $this->server = new \PHPExcel();
        if($sheetname){
            $this->server->getActiveSheet()->setTitle($sheetname);
        }
        return $this->server->getActiveSheet();
    }

    /**
     * 创建一个sheet,根据sheet名称，并返回当前的sheet信息
     * @param $sheetname
     * @return mixed
     * User kuoqi
     * Datetime 2022/4/2 16:07
     * Remark
     */
    public function createSheet($sheetname){
        //$phpexcel = new \PHPExcel();
        //$phpexcel->setActiveSheetIndexByName($sheetname);
        $sheet = $this->server->createSheet();
        $sheet->setTitle($sheetname);
        //$phpexcel->createSheet(0);
        //$sheet = new \PHPExcel_Worksheet($this);
        //$this->server->setActiveSheetIndexByName($sheetname);
        return $sheet;
    }

    /**
     * 根据名字使用一个sheet作为当前活跃的sheet
     * @param $sheetname
     * @return $this
     * User kuoqi
     * Datetime 2022/4/2 16:08
     * Remark
     */
    public function useSheet($sheetname){
        $this->server->setActiveSheetIndexByName($sheetname);
        return $this;
    }

    /**
     * 设置单元格的值，一般为文本
     * @param $cell
     * @param $text
     * @return mixed
     * User kuoqi
     * Datetime 2022/4/2 16:15
     * Remark
     */
    public function setCellText($cell,$text,$options = array()){
        return $this->server->getActiveSheet()->setCellValue($cell,$text);
    }

    /**
     * 写入一个图片
     * createby kuoqi at 2022/9/19 16:39
     * @param $cell
     * @param $url
     * @param array $options
     * @throws \PHPExcel_Exception
     */
    public function setCellImage($cell,$url,$options = array()){
        $defopts = array('x'=>0,'y'=>0,'w'=>0,'h'=>0);
        $config = array_merge($defopts,$options);
        $objDrawing = new \PHPExcel_Worksheet_Drawing();
        $image = new \Kuoqi\FileDriver\Image($url);
        $objDrawing->setPath($image->toPath());
        //$objDrawing->setRotation($config['angle']);
        $objDrawing->setOffsetX($config['x']);
        $objDrawing->setOffsetY($config['y']);
        if($config['w']>0 && $config['h']>0){
            $objDrawing->setResizeProportional(false);
            $objDrawing->setWidthAndHeight($config['w'],$config['h']);
        }else if($config['h']>0){
            //$objDrawing->setResizeProportional(false);
            $objDrawing->setHeight($config['h']);
        }else if($config['w']>0){
            //$objDrawing->setResizeProportional(false);
            $objDrawing->setWidth($config['w']);
        }
        $objDrawing->setCoordinates($cell);//设置当前图片的搁置
        $objDrawing->setWorksheet($this->server->getActiveSheet());
    }

    /**
     * 写入一群图片 dx;支持auto,dy支持auto
     * createby kuoqi at 2022/9/19 16:50
     * @param $cell
     * @param $urls
     * @param array $options
     * @throws \PHPExcel_Exception
     */
    public function setCellImages($cell,$urls,$options = array()){
        $defopts = array('x'=>0,'y'=>0,'w'=>0,'h'=>0,'dx'=>0,'dy'=>0,'thumb_size'=>0);
        $config = array_merge($defopts,$options);
        foreach($urls as $k=>$v){
            $objDrawing = new \PHPExcel_Worksheet_Drawing();
            $image = new \Kuoqi\FileDriver\Image($v);
            if($config['thumb_size']){
                $path = $image->thumb($config['thumb_size'],$config['thumb_size'],\Kuoqi\FileDriver\ImageThumb::MODE_MAXAUTO,'F');
                $objDrawing->setPath($path);
            }else{
                $objDrawing->setPath($image->toPath());
            }
            $objDrawing->setOffsetX($config['x']);
            $objDrawing->setOffsetY($config['y']);
            if($config['w']>0 && $config['h']>0){
                $objDrawing->setResizeProportional(false);
                $objDrawing->setWidthAndHeight($config['w'],$config['h']);
            }else if($config['h']>0){
                //$objDrawing->setResizeProportional(false);
                $objDrawing->setHeight($config['h']);
            }else if($config['w']>0){
                //$objDrawing->setResizeProportional(false);
                $objDrawing->setWidth($config['w']);
            }
            $objDrawing->setCoordinates($cell);//设置当前图片的搁置
            $objDrawing->setWorksheet($this->server->getActiveSheet());
            if($config['dx']==='auto'){
                $config['x'] += $objDrawing->getWidth();
            }else{
                $config['x'] += $config['dx'];
            }
            if($config['dy']==='auto'){
                $config['y'] += $objDrawing->getHeight();
            }else{
                $config['y'] += $config['dy'];
            }
        }
    }

    /**
     * 合并单元格
     * @param $ceil
     * @param $eceil
     * @param array $options
     * @return $this
     * User kuoqi
     * Datetime 2022/4/2 18:19
     * Remark
     */
    public function mergeCells($cell,$ecell,$options = array()){
        //合并单元格
        $this->server->getActiveSheet()->mergeCells($cell.':'.$ecell);
        return $this;
    }

    /**
     * 设置行高
     * createby kuoqi at 2022/9/20 13:51
     * @param $row
     * @param int $height
     */
    public function setRowHeight($actRow,$height = 0){
        //getRowDimension
        if($height){
            $this->server->getActiveSheet()->getRowDimension($actRow)->setRowHeight($height);
        }
    }

    /**
     * 设置列宽
     * createby kuoqi at 2022/9/20 14:17
     * @param $actCeil
     * @param int $width
     */
    public function setColumnWidth($actCeil,$width = 0){
        if($width){
            $this->server->getActiveSheet()->getColumnDimension($actCeil)->setWidth($width);
        }
    }

    //设置单元格信息
    public function setCellOptions($cell,$options = array()){
        //设置font
        $objPHPExcel = array();
        $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setName('Candara');

        $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setSize(20);

        $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);

        $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);

        $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);

        $objPHPExcel->getActiveSheet()->getStyle('E1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);

        $objPHPExcel->getActiveSheet()->getStyle('D13')->getFont()->setBold(true);

        $objPHPExcel->getActiveSheet()->getStyle('E13')->getFont()->setBold(true);

        //设置align
        $objPHPExcel->getActiveSheet()->getStyle('D11')->getAlignment()->

                setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

        $objPHPExcel->getActiveSheet()->getStyle('D12')->getAlignment()->

        setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

        $objPHPExcel->getActiveSheet()->getStyle('D13')->getAlignment()->

        setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

        $objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()->

        setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);

        //垂直居中

        $objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()->

        setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

        //设置column的border
        $objPHPExcel->getActiveSheet()->getStyle('A4')->getBorders()->getTop()->

                setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);$objPHPExcel->getActiveSheet()->getStyle('B4')->

                getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

        $objPHPExcel->getActiveSheet()->getStyle('C4')->getBorders()->getTop()->

        setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

        $objPHPExcel->getActiveSheet()->getStyle('D4')->getBorders()->getTop()->

        setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

        $objPHPExcel->getActiveSheet()->getStyle('E4')->getBorders()->getTop()->

        setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

        //设置border的color
        $objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getLeft()->

                getColor()->setARGB('FF993300');

        $objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getTop()->getColor()->setARGB('FF993300');

        $objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getBottom()->getColor()->setARGB('FF993300');

        $objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getTop()->getColor()->setARGB('FF993300');

        $objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getBottom()->getColor()->setARGB('FF993300');

        $objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getRight()->getColor()->setARGB('FF993300');

        //设置填充颜色

        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);

        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->getStartColor()->setARGB('FF808080');

        $objPHPExcel->getActiveSheet()->getStyle('B1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);

        $objPHPExcel->getActiveSheet()->getStyle('B1')->getFill()->getStartColor()->setARGB('FF808080');
        $style = new PhpExcel\Style($options);
        var_dump($style);exit();
        //关于图片
        $objDrawing = new PHPExcel_Worksheet_Drawing();$objDrawing->setName('Logo');

        $objDrawing->setDescription('Logo');$objDrawing->setPath('./images/officelogo.jpg');

        $objDrawing->setHeight(36);$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());

        $objDrawing = new PHPExcel_Worksheet_Drawing();$objDrawing->setName('Paid');

        $objDrawing->setDescription('Paid');$objDrawing->setPath('./images/paid.png');

        $objDrawing->setCoordinates('B15');$objDrawing->setOffsetX(110);

        $objDrawing->setRotation(25);$objDrawing->getShadow()->setVisible(true);

        $objDrawing->getShadow()->setDirection(45);$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
    }

    /**
     * 设置属性format
     * @param $format data_list:输出为二维数组模式 download:输出为可下载文件
     * User kuoqi
     * Datetime 2022/4/2 15:18
     * Remark
     */
    public function setFormat($format){
        $this->format = $format;
    }

    /**
     * 设置一个文件名称
     * @param $filename
     * User kuoqi
     * Datetime 2022/4/2 16:17
     * Remark
     */
    public function setFilename($filename){
        $this->filename = $filename;
    }

    /**
     * 尝试输出
     * @return array
     * @throws Exception
     * User kuoqi
     * Datetime 2022/3/30 15:50
     * Remark
     */
    public function output(){
        switch($this->format){
            case 'data_list':$this->flushToDatalist();break;//输出为二维数组模式
            case 'download':$this->flushToDownload();break;//输出为可下载文件
        }
        return $this->output_data;
    }

    /**
     * 输出为数组模式
     * User kuoqi
     * Datetime 2022/3/30 11:30
     * Remark
     */
    public function flushToDatalist(){
        //获取标题信息
        if($this->opts['sheet_name']){
            $sheetData = $this->server->getSheetByName($this->opts['sheet_name'])->toArray(null,true,true,true);
        }else{
            $sheetData = $this->server->getSheet(0)->toArray(null,true,true,true);
        }

        $heades = array();
        $datas = array();
        //获取标题的相关信息
        if($this->opts['is_row']){
            foreach ($sheetData as $k=>$it){
                if($k<$this->opts['is_row']){
                    continue;
                }else if($k==$this->opts['is_row']){
                    //这个是具体的定义
                    //采集一行数据
                    foreach($it as $itn=>$item){
                        if(isset($this->opts['cols'][$item])&&$this->opts['cols'][$item]){
                            //字段存在
                            $heades[$itn] = $this->opts['cols'][$item];
                        }
                    }
                }else{
                    //数据采集
                    $ite = array();
                    $needit = true;
                    foreach($heades as $colname=>$colval){
                        //判定是否必须字段存在，不存在则跳过改行
                        if(($this->opts['col_require'])&&($colval==$this->opts['col_require'])&&(empty($it[$colname]))){
                            $needit = false;
                            continue;
                        }
                        if(isset($it[$colname])){
                            //数据存在
                            $ite[$colval] = $it[$colname];
                        }else{
                            //数据不存在
                            $ite[$colval] = null;
                        }
                    }
                    $needit&&($datas[] = $ite);
                }
            }
        }else{
            throw new \Kuoqi\Exception('这种情况尚未初始化，请更新');
        }
        return $this->output_data = $datas;
    }

    public function flushToWeb(){
        $objWriter = new \PHPExcel_Writer_Excel5($this->server);
        header("Pragma: public");header("Expires: 0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");;
        header('Content-Disposition:attachment;filename="'.$this->filename.'.xls"');
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');
    }

    /**
     * 输出为可下载文件
     * User kuoqi
     * Datetime 2022/4/2 15:38
     * Remark
     */
    public function flushToDownload(){
        // Redirect output to a client’s web browser (Excel2007)
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="'.$this->filename.'.xlsx"');
        header('Cache-Control: max-age=0');
        // If you're serving to IE 9, then the following may be needed
        header('Cache-Control: max-age=1');

        // If you're serving to IE over SSL, then the following may be needed
        header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
        header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header ('Pragma: public'); // HTTP/1.0

        $objWriter = \PHPExcel_IOFactory::createWriter($this->server, 'Excel2007');
        $objWriter->save('php://output');
        exit;
    }

    /**
     * 下载为pdf
     * User kuoqi
     * Datetime 2022/4/2 15:48
     * Remark
     */
    public function flushToDownloadWithPdf(){
        // Redirect output to a client’s web browser (PDF)
        header('Content-Type: application/pdf');
        header('Content-Disposition: attachment;filename="01simple.pdf"');
        header('Cache-Control: max-age=0');

        $objWriter = \PHPExcel_IOFactory::createWriter($this->server, 'PDF');
        $objWriter->save('php://output');
        exit;
    }

    public function flushToDownloadbak(){
        // Set document properties
        $objPHPExcel = new \PHPExcel();
        $objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
            ->setLastModifiedBy("Maarten Balliauw")
            ->setTitle("Office 2007 XLSX Test Document")
            ->setSubject("Office 2007 XLSX Test Document")
            ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
            ->setKeywords("office 2007 openxml php")
            ->setCategory("Test result file");


// Add some data
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', 'Hello')
            ->setCellValue('B2', 'world!')
            ->setCellValue('C1', 'Hello')
            ->setCellValue('D2', 'world!');

// Miscellaneous glyphs, UTF-8
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A4', 'Miscellaneous glyphs')
            ->setCellValue('A5', 'éàèùâêîôûëïüÿäöüç');

// Rename worksheet
        $objPHPExcel->getActiveSheet()->setTitle('Simple');


// Set active sheet index to the first sheet, so Excel opens this as the first sheet
        $objPHPExcel->setActiveSheetIndex(0);


        // Redirect output to a client’s web browser (Excel2007)
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="01simple.xlsx"');
        header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
        header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
        header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
        header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header ('Pragma: public'); // HTTP/1.0

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save('php://output');
        exit;
    }

    /**
     * 根据文件返回返回文件的具体excel的操作
     * createby kuoqi at 2022/9/19 18:37
     * @param $file
     * @return PhpExcel
     * @throws \Kuoqi\Error
     * @throws \PHPExcel_Reader_Exception
     */
    public static function createByFile($file){
        $file = Route::str2rootpath($file);
        $excel = new self();
        $excel->server = \PHPExcel_IOFactory::load($file);
        //$excel->server->getSheetByName()
        //$sheetData = $excel->server->getActiveSheet()->toArray(null,true,true,true);
        return $excel;
    }

}